<?php

function __autoload($class_name) {
  require_once "$class_name.php";
}

abstract class FormWriter {
  # This is the prefix used for hidden fields in the database specification.
  const hide_char='.';

  /*
    Support for partially underlined qualified retrieval.
    There are two syntaxes: prepend an underscore to underline single 
    characters or wrap with underscore to underline an entire word or 
    character.
    I chose to implement the latter because allows for using long 
    meaningful variable names without requiring you to prefix each 
    character with a _ and simplifies double underlining (used to 
    express grouping).
    E.g. _JONES_ is easier to type than _J_O_N_E_S. And __JONES__ is 
    even easier than __J__O__N__E__S, isn't it?
    I've also implemented a shortcut: a _ at the end of the string
    can be omitted.
   
    I will give as a comment the regexps used to implement the first 
    and second syntax without shortcut.
   */
  # Other syntaxes: /_[A-Za-z0-9]/, /_[A-Za-z0-9]+_/
  const var_re = '/_[A-Za-z0-9]+_?/';

  const group_var_re = '/__[A-Za-z0-9]+(?:__)?/';

  const expr_re = '/^\(.*\)$/';

  const funct_re = '/(?:(UNQ?|ALL)\\.)?(CNT|COUNT|SUM|MAX|MIN|AVG)(?:\\.(UNQ?|ALL))?\\.(_[A-Z0-9]+_?)(?:\\.(UNQ?|ALL))?/';

  public static $table_def_opts = array(
    'caption' => '',
    /*
      HTML to print when the table is empty. If NULL an empty table is 
      generated. An empty table does not have data rows or columns, only one 
      header row/column, unless header option is FALSE).
     */
    'empty'   => NULL,
    /*
      Whether the table method should free the result
      memory for the passed mysqli statement handle
     */
    'free'    => TRUE,
    /*
      By default group is NULL, which means each field will make
      a group by itself. Use an empty array if you want no grouping.
      You can also provide your own <col /> and/or <colgroup /> structure by
      making this option an HTML string. The latter is only useful
      in horizontal display mode, because in vertical one we only have two 
      columns.
      TODO: row grouping i.e. multiple tbody tags will make sense in this case
      By default don't group in vertical mode. Support vertical mode with or
      without wrapping (implies horizontal scrolling)
     */
    'group'   => NULL,
    /*
      Whether to print an horizontal or vertical header
      with the names of the columns in the result set.

      TODO: if FALSE, what should be used for sorting?
			Better using a separate sorting control.
			Choose between server and client-side sorting.
     */
    'header'  => TRUE,
    # TODO: implement vertical and tiled layout
    'layout'  => 'horiz',
    'one_tbl' => TRUE,  # honoured only when dir equals 'vert'
    /*
      How every rows the header should be repeated.
      Ignored if header option is FALSE.
     */
    'repeat'  => 0,
    /*
      Allows custom format printing for all or some fields given as keys.
      The corresponding value is the PHP code that will be used to print them.
      In this code you can access the variables $row, $field and $value.
      If a value is NULL the corresponding column will be not displayed.
     */
    'custom'  => array(),
		/*
		  List of fields that make up an unique key for the table.
		  If not empty the table can we used as a selection list.
		 */
		'uniq'    => array(),

    # Paginator options.
    /*
      If a single page as more than one paginator control, each must have a 
      different id, so this option should be overriden.
      Check id token syntax: as per HTML specs must begin with a letter 
      ([A-Za-z]) and may be followed by any number of letters, digits ([0-9]), 
      hyphens ("-"), underscores ("_"), colons (":"), and periods (".").
     */
    'id'      => 'fw_page',
    /*
      Url where update AJAX requests will be directed. If empty the current 
      script file name will be used that is the shortest relative path.
     */
    'url'     => '',
    # Where to display the pager: top, bottom, both.
    'pager'   => 'bottom',
    /*
      How many page numbers to display on the
      right and left of the current one.
     */
    'delta'   => 10,
    # How many rows to display in a page.
    'row_cnt' => 10
  );

  # Connection to a MySQL database.
  public $mysqli;

  /*
    Automatically select between different binary and string data types based 
    on these three attributes (NULL means "don't care") given as indexes:
   
      maxlen: maximum number of characters or bytes (for binary data) to store
      rtrim: whether trailing spaces are removed or retained
      storage: where and how the column data are stored
   
    We don't rely on MySQL silent column changes because
    they do not occur if strict SQL mode is enabled.
   */ 
  /*
    TODO: when requested rtrim is TRUE and resultant data type is different 
    from CHAR, right trimming should be perfomed by FormWriter on 
    INSERT/UPDATE; when rpad is TRUE and resultant data type is different from 
    BINARY, right padding should be done by FormWriter on SELECT.
   */
  protected static $str_type_hinter = array(
    'BIN' => array(
      # maxlen
      '<=255' => array(
        # rpad
        NULL => array(
          # storage => data_type or array(data_type, warning)
          NULL       => 'VARBINARY(%u)',
          'fixed'    => 'BINARY(%u)',
          'variable' => 'VARBINARY(%u)',
          'off-page' => 'TINYBLOB'
        ),
        FALSE => array(
          NULL       => 'VARBINARY(%u)',
          'fixed'    => array('VARBINARY(%u)', 'storage fixed ignored'),
          'variable' => 'VARBINARY(%u)',
          'off-page' => 'TINYBLOB'
        ),
        TRUE => array(
          NULL       => 'BINARY(%u)',
          'fixed'    => 'BINARY(%u)',
          'variable' => 'VARBINARY(%u)',
          'off-page' => 'TINYBLOB'
        )
      ),
      '<=65535' => array(
        NULL => array(
          NULL       => 'VARBINARY(%u)',
          'fixed'    => array('VARBINARY(%u)', 'storage fixed ignored'),
          'variable' => 'VARBINARY(%u)',
          'off-page' =>  'BLOB'
        ),
        FALSE => array(
          NULL       => 'VARBINARY(%u)',
          'fixed'    => array('VARBINARY(%u)', 'storage fixed ignored'),
          'variable' => 'VARBINARY(%u)',
          'off-page' => 'BLOB'
        ),
        TRUE => array(
          NULL       => 'VARBINARY(%u)',
          'fixed'    => array('VARBINARY(%u)', 'storage fixed ignored'),
          'variable' => 'VARBINARY(%u)',
          'off-page' => 'BLOB'
        )
      ),
      '<=16777215' => array(
        NULL => array(
          NULL       => 'MEDIUMBLOB',
          'fixed'    => array('MEDIUMBLOB', 'storage fixed ignored'),
          'variable' => array('MEDIUMBLOB', 'storage variable ignored'),
          'off-page' => 'MEDIUMBLOB'
        ),
        FALSE => array(
          NULL       => 'MEDIUMBLOB',
          'fixed'    => array('MEDIUMBLOB', 'storage fixed ignored'),
          'variable' => array('MEDIUMBLOB', 'storage variable ignored'),
          'off-page' => 'MEDIUMBLOB'
        ),
        TRUE => array(
          NULL       => 'MEDIUMTBLOB',
          'fixed'    => array('MEDIUMBLOB', 'storage fixed ignored'),
          'variable' => array('MEDIUMBLOB', 'storage variable ignored'),
          'off-page' => 'MEDIUMBLOB'
        )
      ),
      '<=4294967295' => array(
        NULL => array(
          NULL       => 'LONGBLOB',
          'fixed'    => array('LONGBLOB', 'storage fixed ignored'),
          'variable' => array('LONGBLOB', 'storage variable ignored'),
          'off-page' => 'LONGBLOB'
        ),
        FALSE => array(
          NULL       => 'LONGTEXT',
          'fixed'    => array('LONGTEXT', 'storage fixed ignored'),
          'variable' => array('LONGTEXT', 'storage variable ignored'),
          'off-page' => 'LONGTEXT'
        ),
        TRUE => array(
          NULL       => 'LONGBLOB',
          'fixed'    => array('LONGBLOB', 'storage fixed ignored'),
          'variable' => array('LONGBLOB', 'storage variable ignored'),
          'off-page' => 'LONGBLOB'
        )
      )
    ),
    'STR' => array(
      # maxlen
      '<=3' => array(
        # rtrim
        NULL => array(
          # storage => data_type or array(data_type, warning)
          NULL       => 'CHAR(%u)',
          'fixed'    => 'CHAR(%u)',
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        ),
        FALSE => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => array('VARCHAR(%u)', 'storage fixed ignored'),
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        ),
        TRUE => array(
          NULL       => 'CHAR(%u)',
          'fixed'    => 'CHAR(%u)',
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        ),
      ),
      '<=255' => array(
        NULL => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => 'CHAR(%u)',
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        ),
        FALSE => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => array('VARCHAR(%u)', 'storage fixed ignored'),
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        ),
        TRUE => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => 'CHAR(%u)',
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TINYTEXT'
        )
      ),
      '<=65535' => array(
        NULL => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => array('VARCHAR(%u)', 'storage fixed ignored'),
          'variable' => 'VARCHAR(%u)',
          'off-page' =>  'TEXT'
        ),
        FALSE => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => array('VARCHAR(%u)', 'storage fixed ignored'),
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TEXT'
        ),
        TRUE => array(
          NULL       => 'VARCHAR(%u)',
          'fixed'    => array('VARCHAR(%u)', 'storage fixed ignored'),
          'variable' => 'VARCHAR(%u)',
          'off-page' => 'TEXT'
        )
      ),
      '<=16777215' => array(
        NULL => array(
          NULL       => 'MEDIUMTEXT',
          'fixed'    => array('MEDIUMTEXT', 'storage fixed ignored'),
          'variable' => array('MEDIUMTEXT', 'storage variable ignored'),
          'off-page' => 'MEDIUMTEXT'
        ),
        FALSE => array(
          NULL       => 'MEDIUMTEXT',
          'fixed'    => array('MEDIUMTEXT', 'storage fixed ignored'),
          'variable' => array('MEDIUMTEXT', 'storage variable ignored'),
          'off-page' => 'MEDIUMTEXT'
        ),
        TRUE => array(
          NULL       => 'MEDIUMTEXT',
          'fixed'    => array('MEDIUMTEXT', 'storage fixed ignored'),
          'variable' => array('MEDIUMTEXT', 'storage variable ignored'),
          'off-page' => 'MEDIUMTEXT'
        )
      ),
      '<=4294967295' => array(
        NULL => array(
          NULL       => 'LONGTEXT',
          'fixed'    => array('LONGTEXT', 'storage fixed ignored'),
          'variable' => array('LONGTEXT', 'storage variable ignored'),
          'off-page' => 'LONGTEXT'
        ),
        FALSE => array(
          NULL       => 'LONGTEXT',
          'fixed'    => array('LONGTEXT', 'storage fixed ignored'),
          'variable' => array('LONGTEXT', 'storage variable ignored'),
          'off-page' => 'LONGTEXT'
        ),
        TRUE => array(
          NULL       => 'LONGTEXT',
          'fixed'    => array('LONGTEXT', 'storage fixed ignored'),
          'variable' => array('LONGTEXT', 'storage variable ignored'),
          'off-page' => 'LONGTEXT'
        )
      )
    )
  );

  /*
     One-liner templates for form and table layout. Just override what you
     need to.  You can also define a custom layout (multiple allowed).
   */
  # TODO: helper method to print only if setted
  # TODO: flat vs nested vs mixed hierarchy
  public static $template = array(
    'form' => array(
      'start' => "<form>\n",
      'table' => array(
        'start'      => "<table>\n",
        'push_start' => "<tr>\n<td></td>\n<td>",
        'push_end'   => "</td>\n</tr>\n",
        'grp_start'  =>  "<tr>\n",
        'grp_end'    =>  "</tr>\n",
        'label'      => "<td><label for=\"%1\$s\">%2\$s</label>%3\$s</td>\n",
        'nolabel'    => "<td>%1\$s</td>\n",
        'required'   => '<sup>*</sup>',
        'ctrl_start' => '<td>',
        'ctrl_end'   => "</td>\n",
        'end'        => "</table>\n"
      ),
      'break' => array(
        'start'    => '<p>',
        'label'    => '<label>%2$s',
        'ctrl_end' => "</label><br />\n",
        'end'      =>  "</p>\n"
      ),
      'space' => array(
        'start'    => '<p>',
        'label'    => '<label>%2$s',
        'ctrl_end' => '</label>',
        'end'      =>  "</p>\n"
      ),
      'end'   => "</form>\n"
    ),
    # TODO: try a vertical table
    'table' => array(
      'start'      => "<table border=\"2\" frame=\"hsides\" rules=\"groups\" cellpadding=\"2\" %1\$s>\n",
      'caption'    => "<caption>%1\$s</caption>\n",
      'head_start' => "<thead>\n<tr>\n",
      'head_end'   => "</tr>\n</thead>\n",
      'body_start' => "<tbody>\n",
      'body_end'   => "</tbody>\n",
      'row_start'  => "<tr class=\"%1\$s\">\n",
      'head'       => "<th>%1\$s</th>\n",
			'checkbox'   => '<td><input type="checkbox"></td>',
      /*
        TODO: title attribute does not work in current common browsers
        when applied to col or colgroup even if the specs say it should.
        Unfortunately, for now we have to repeat the title for each cell.
				We have not better luck with onmouseover if we'd like to display
				the field name in the status bar.
       */
      'data'       => "<td title=\"%1\$s\">%2\$s</td>\n",
      'null'       => "<td>&nbsp;</td>\n",
      'row_end'    => "</tr>\n",
      'end'        => "</table>\n"
    ),
    'pager' => array(
      'start'   => "<div id=\"%1\$s\" class=\"fw_page\">\n",
      'first'   => '<a href="#" id="%1$s_first" title="page 1 of %2$u" class="fw_first">&#8656;</a>',
      'back'    => '<a href="#" id="%1$s_back" title="page %2$u of %3$u" class="fw_back">&#8592;</a>',
      'number'  => '<a href="#" id="%1$s_%2$u" title="page %2$u of %3$u" class="fw_number">%2$u</a>',
      'current' => '<span id="%1$s_current" class="fw_current">%2$u</span>',
      'forward' => '<a href="#" id="%1$s_forward" title="page %2$u of %3$u" class="fw_forward">&#8594;</a>',
      'last'    => '<a href="#" id="%1$s_last" title="page %2$u of %2$u" class="fw_last">&#8658;</a>',
      'end'     => "</div>\n"
    )
  );

  public function __construct() {
    $this->resolve_all_as();

    # TODO: do this only on request (FormWriter may work also without a RDBMS):
    # add a no-create option to avoid overhead and a re-create option to force recreate
    $this->connect_dbms();
    $this->create_db();
  }

  public function connect_dbms() {
    $this->mysqli = new mysqli(
      $this->def('host', ini_get('mysqli.default_host')),
      $this->def('user', ini_get('mysqli.default_user')),
      $this->def('pw', ini_get('mysqli.default_pw'))
    );
    # TODO: when an error occurs execution resumes. Should we stop?
  }

  public static function trim_value(&$value) {
    $value = trim($value);
  }

  public static function array_conv($delimiter, &$var) {
    if (!is_array($var)) {
      $var = explode($delimiter, $var);
      # TODO: use 'self::trim_value' instead of array if will be supported
      array_walk($var, array('self', 'trim_value'));
    }
  }

  protected static function int_type_hinter($col_def) {
    # TODO: allow to select different integer data types without remembering the interval

    /*
      Select the best MySQL integer type to use based on range 
      constraints. This minimizes the amount of storage required.
     */
    
    # Default range is that of a 32-bit signed integer.
    $minval = isset($col_def['minval']) ? $col_def['minval'] : -2147483648;
    $maxval = isset($col_def['maxval']) ? $col_def['maxval'] : 2147483647;

    /*
      When a positive number constraint is given and both a signed 
      and unsigned type will apply, we prefer unsigned ones.
      This is achieved simply by testing first the unsigned interval.
     */
    if ($minval >= 0 && $maxval <= 255) {
      $data_type = 'TINYINT(%u) UNSIGNED';
    } elseif ($minval >= -128 && $maxval <= 127) {
      $data_type = 'TINYINT(%u)';
    } elseif ($minval >= 0 && $maxval <= 65535) {
      $data_type = 'SMALLINT(%u) UNSIGNED';
    } elseif ($minval >= -32768 && $maxval <= 32767) {
      $data_type = 'SMALLINT(%u)';
    } elseif ($minval >= 0 && $maxval <= 16777215) {
      $data_type = 'MEDIUMINT(%u) UNSIGNED';
    } elseif ($minval >= -8388608 && $maxval <= 8388607) {
      $data_type = 'MEDIUMINT(%u)';
    /*
      Please note that PHP does not support unsigned
      integers and the maximum integer value is 2147483647
      but higher values are automatically interpreted as
      a float, so the following comparison works.
     */
    } elseif ($minval >= 0 && $maxval <= 4294967295) {
      $data_type = 'INT(%u) UNSIGNED';
    } elseif ($minval >= -2147483648 && $maxval <= 2147483647) {
      $data_type = 'INT(%u)';
    } elseif ($minval < 0) {
      $data_type = 'BIGINT(%u)';
    } else {
      $data_type = 'BIGINT(%u) UNSIGNED';
    }

    return sprintf($data_type, isset($col_def['length']) ?
      $col_def['length'] : max(strlen($minval), strlen($maxval)));
  }

  # TODO: creating a table with foreign keys fails if the target table does not exists yet.
  public function create_db() {
    # Create database and tables if do not already exists.

    $database = $this->def('name');
    # TODO: what character set to use?
    # TODO: a backtick in variable should be doubled
		# TODO: collation (default is latin1_swedish_ci)
    $this->mysqli->query("CREATE DATABASE IF NOT EXISTS `$database`");
    $this->mysqli->select_db($database);

    foreach ($this->db as $tbl_name => $tbl_def) {
      if ($tbl_name[0] == self::hide_char) {
        continue;
      }

      $create_defs = array();
      $has_primary_key = FALSE;
      foreach ($tbl_def as $col_name => $col_def) {
        if (strtolower($col_name) == self::hide_char . 'key') {
          if (!is_array($col_def)) {
            $col_def = array($col_def);
          }
          foreach ($col_def as $index_def) {
            if (is_array($index_def)) {
              if (!isset($index_def['type'])) { 
                $index_def['type'] = isset($index_def['refs']) ?
                  'FOREIGN'
                :
                  ($has_primary_key ? 'INDEX' : 'PRIMARY');
              }
            } elseif (strpos($index_def, '.')) {
              # TODO: try to reuse array_conv
              list($table, $refs) = explode('.', $index_def);
              $index_def = array('type' => 'FOREIGN',
                'refs' => $refs, 'table' => $table);
            } else {
              $index_def = array(
                'type' => $has_primary_key ? 'INDEX' : 'PRIMARY',
                'cols' => $index_def
              );
            }
            # set an index prefix length
            $index_len = isset($index_def['len']) ?
              '(' . $index_def['len'] . ')' : '';

            # TODO: implement other key types (as fulltext on MyISAM table 
            # type, see CREATE TABLE syntax)
            # TODO: support prefix length for keys
            # TODO: warning when two primary keys are defined
            switch ($index_def['type'] = strtoupper($index_def['type'])) {
              case 'PRIMARY':
                $has_primary_key = TRUE;
                $create_defs[] = sprintf('  PRIMARY KEY (%s%s)',
                  self::col_list($index_def['cols']), $index_len);
              break;

              case 'FOREIGN':
                if (!isset($index_def['cols'])) {
                  $index_def['cols'] = $index_def['refs'];
                }
                $create_defs[] = sprintf('  FOREIGN KEY %s(%s%s) REFERENCES `%s` (%s)',
                  isset($index_def['name']) ? "`{$index_def['name']}` " : '',
                  self::col_list($index_def['cols']), $index_len,
                  isset($index_def['table']) ? $index_def['table'] : $tbl_name,
                  self::col_list($index_def['refs'])
                );
              break;

              case 'INDEX':
              case 'UNIQUE':
                $create_defs[] = sprintf('  %s %s(%s%s)', $index_def['type'],
                  isset($index_def['name']) ? "`{$index_def['name']}`" : '',
                  self::col_list($index_def['cols']), $index_len
                );
              break;
            }
          }
        } else {
          $create_def = "  `$col_name` ";
          switch (strtoupper($col_def['type'])) {
            case 'STR':
              # TODO: index prefix length mandatory for BLOG and TEXT; they 
              # cannot have default values

              if (!isset($col_def['rtrim'])) {
                $col_def['rtrim'] = NULL;
              }
              $maxlen = isset($col_def['maxlen']) ? $col_def['maxlen'] : 255;
              if (!isset($col_def['storage'])) {
                if (isset($col_def['minlen']) && $col_def['minlen'] == $maxlen && $maxlen <= 255) {
                  $col_def['storage'] = 'fixed';
                } else {
                  $col_def['storage'] = NULL;
                }
              }
              if ($maxlen <= 3) {
                $cond = '<=3';
              } elseif ($maxlen <= 255) {
                $cond = '<=255';
              } elseif ($maxlen <= 16777215) {
                $cond = '<=16777215';
              } else {
                $cond = '<=4294967295';
              }
              $data_type = self::$str_type_hinter['STR'][$cond][$col_def['rtrim']][$col_def['storage']];
              if (is_array($data_type)) {
                trigger_error($data_type[1]);
                $data_type = $data_type[0];
              }
              $create_def .= sprintf($data_type, $maxlen);
            break;

            case 'INT':
              $create_def .= self::int_type_hinter($col_def);
            break;

            # TODO: Floating-point and fixed-point types also can be UNSIGNED. 
            # As with integer types, this attribute prevents negative values 
            # from being stored in the column. However, unlike the integer 
            # types, the upper range of column values remains the same.

            # fixed-point exact numeric type
            case 'DEC':
              # TODO: 
            break;

            # floating-point approximate numeric type
            case 'REAL':
              # precision in bits (p) - standard SQL (also used by ODBC)
              if (isset($col_def['bits'])) {
                $create_def .= "FLOAT({$col_def['bits']})";
              } else {
                # TODO: the maximum possible value of scale is 30, but should 
                # be no greater than M-2

                /*
                  MySQL extension: the precision (M) is the total number of 
                  digits that can be stored the scale (D) is the number of 
                  digits following the decimal point.
                 */
 
                if (isset($col_def['precision']) && isset($col_def['scale'])) {
                  # TODO: allow to select between FLOAT and DOUBLE
                  $create_def .= "DOUBLE({$col_def['precision']},{$col_def['scale']})";
                } else {
                  /*
                    For maximum portability, code requiring storage of 
                    approximate numeric data values should use FLOAT or DOUBLE 
                    PRECISION with no specification of precision or number of 
                    digits.
                   */
                  # TODO: allow to select between FLOAT and DOUBLE
                  $create_def .= 'DOUBLE';
                }
              }
            break;

            case 'DATE':
              $create_def .= 'DATE';
            break;

            case 'YEAR':
              /*
                We do not support a display width of two characters,
                because a two-digit year is ambiguous.
               */

              $minval = isset($col_def['minval']) ? $col_def['minval'] : 1901;
              $maxval = isset($col_def['maxval']) ? $col_def['maxval'] : 2155;

              if ($minval >= 1901 && $maxval <= 2155) {
                $create_def .= 'YEAR';
              } else {
                # as INT
                $create_def .= self::int_type_hinter($col_def);
              }
            break;

              # TODO: add TIME and DATETIME types.
              /*
                We do not support TIMESTAMP to avoid the year 2038 problem:
                http://en.wikipedia.org/wiki/Year_2038_problem
               */

            case 'BOOL':
              $create_def .= 'BOOL';
            break;

            case 'PWD':
              /*
                We don't support MD5 for password because is insecure. We use SHA1.
                Note we store the binary version of then hash, we don't need
                to make the field longer by using the character encoding of it.
               */
              # TODO: allow to choose between BINARY and VARBINARY
              $create_def .= "BINARY(40)";
            break;

            case 'URL':
              /*
                2083 is the least URL length as supported by Microsoft 
                Internet Explorer (Browser). For details see: 
                http://www.boutell.com/newfaq/misc/urllength.html
                About keys on URL fields see: 
                http://forums.mysql.com/read.php?22,154827,154827
               */
              $create_def .= 'VARCHAR(2083) CHARACTER SET ascii';
            break;

            case 'EMAIL':
              /*
                the local part (before the @ sign) must not exceed 64 characters
                the domain part cannot be longer than 255 characters
                64+1+255=320
               */
              $create_def .= 'VARCHAR(320)';
            break;

            case 'ENUM':
              # TODO: read mysql doc about enums
              # TODO: espansion of vals is required even when a no-create option is set
              self::array_conv(',', $col_def['vals']);
              $create_def .= 'ENUM(' . implode(',', $col_def['vals']) . ')';
            break;

            case 'BIN':
              if (!isset($col_def['rpad'])) {
                $col_def['rpad'] = NULL;
              }
              $maxlen = $col_def['maxlen'];
              if (!isset($col_def['storage'])) {
                if (isset($col_def['minlen']) && $col_def['minlen'] == $maxlen && $maxlen <= 255) {
                  $col_def['storage'] = 'fixed';
                } else {
                  $col_def['storage'] = NULL;
                }
              }
              if ($maxlen <= 255) {
                $cond = '<=255';
              } elseif ($maxlen <= 16777215) {
                $cond = '<=16777215';
              } else {
                $cond = '<=4294967295';
              }
              $data_type = self::$str_type_hinter['BIN'][$cond][$col_def['rpad']][$col_def['storage']];
              if (is_array($data_type)) {
                trigger_error($data_type[1]);
                $data_type = $data_type[0];
              }
              $create_def .= sprintf($data_type, $maxlen);
            break;

            case 'HASH':
              if (! isset($col_def['funct'])) {
                $col_def['funct'] = 'SHA1';  
              }
              switch (strtoupper($col_def['funct'])) {
                case 'SHA1':
                  $minlen = 40;
                break;

                case 'MD5':
                  $minlen = 32;
                break;

                default:
                  # TODO: error handling
                break;
              }
              $maxlen = $minlen;

              /*
                Note we store the binary version of then hash, we don't need
                to make the field longer by using the character encoding of it.
               */
              # TODO: allow to choose between BINARY and VARBINARY
              $create_def .= "BINARY($maxlen)";
            break;
          }
          # TODO: support optional BINARY ATTRIBUTE. See where allowed.
          # TODO: charset and collate support. See where allowed.
          if (isset($col_def['zerofill']) && !$col_def['zerofill']) {
            $create_def .= ' ZEROFILL';
          }
          # Columns have NULL as default unless specified by NOT NULL.
          if (isset($col_def['null']) && !$col_def['null']) {
            $create_def .= ' NOT NULL';
          }
          if (isset($col_def['def'])) {
            $create_def .= ' DEFAULT ' . $cold_def['def'];
          }
          if (isset($col_def['autoinc']) && $col_def['autoinc']) {
            $create_def .= ' AUTO_INCREMENT';
          }
          if (isset($col_def['comment'])) {
            $create_def .= sprintf(" COMMENT '%s'",
              $this->mysqli->real_escape_string($col_def['comment']));
          }
          $create_defs[] = $create_def;
        }
      }
      # TODO: this is a DEBUG version of the code below
      /*
      $create_table = sprintf("CREATE TABLE IF NOT EXISTS `%s` (\n%s\n) ENGINE=InnoDB",
        $tbl_name, implode(",\n", $create_defs));
      echo "<pre>$create_table</pre>";
      $this->mysqli->query($create_table);
       */

      $this->mysqli->query(sprintf(
        "CREATE TABLE IF NOT EXISTS `%s` (\n%s\n) ENGINE=InnoDB",
        $tbl_name, implode(",\n", $create_defs)));
    }
  }

  protected function def($key, $val='') {
    $key = self::hide_char . $key;
    if (isset($this->db[$key])) {
      return $this->db[$key];
    }
    return $val;
  }

  public static function col_list($index_col_name) {
    self::array_conv(',', $index_col_name);
    $col_list = array();
    foreach ($index_col_name as $index_col_name) {
      $col_list[] = $index_col_name != '*' ?
        "`$index_col_name`" : '*';
    }
    return implode(',', $col_list);
  }

  # TODO: error checking on not existent referred table and/or field
  protected function resolve_all_as() {
    foreach ($this->db as $tbl_name => &$tbl_def) {
      if ($tbl_name[0] == self::hide_char) {
        continue;
      }

      foreach ($tbl_def as $col_name => &$col_def) {
        if ($col_name[0] == self::hide_char) {
          continue;
        }
        if (!is_array($col_def)) {
          if (strpos($col_def, self::hide_char) === false) {
            # TODO: each type must have default values for other sql-needed parameters
            $col_def = array('type' => $col_def);
          } else {
            $col_def = array(self::hide_char . 'as' => $col_def);
          }
        }
        if (isset($col_def[self::hide_char . 'as'])) {
          # Support .field meaning "as field in the same table"
          if ($col_def[self::hide_char . 'as'][0] == self::hide_char) {
            $col_def[self::hide_char . 'as'] = $tbl_name . $col_def[self::hide_char . 'as'];
          }

          $this->resolve_as($col_def);
        }
      }
    }
  }

  protected function resolve_as(&$col_def) {
    $as = self::hide_char . 'as';
    if (isset($col_def[$as])) {
      # TODO: cerca di riutilizzare array_conv  
      list($as_tbl, $as_col) = explode('.', $col_def[$as]);
      $col_def += $this->resolve_as($this->db[$as_tbl][$as_col]);
      unset($col_def[$as]);
    } else {
      # TODO: add other fields that have to be inherited
      foreach (array('maxlen', 'minlen', 'minval', 'null', 'size', 'type') as $def) {
        if (isset($col_def[$def])) {
          $defs[$def] = $col_def[$def];
        }
      }
      return $defs;
    }
  }

  /*
    Alternative implementation that does not require
    references in resolve_all_as. Call with:
      $this->resolve_as($tbl_name, $col_name);
   */
  /*
  protected function resolve_as($tbl_name, $col_name) {
    $as = self::hide_char . 'as';
    $col_def = &$this->db[$tbl_name][$col_name];
    if (isset($col_def[$as])) {
      list($as_tbl, $as_col) = explode('.', $col_def[$as]);
      $col_def += $this->resolve_as($as_tbl, $as_col);
      unset($col_def[$as]);
    } else {
      # TODO: add other fields that have to be inherited
      foreach (array('maxlen', 'minlen', 'minval', 'null', 'size', 'type') as $def) {
        if (isset($col_def[$def])) {
          $defs[$def] = $col_def[$def];
        }
      }
      return $defs;
    }
  }
   */

  public function get_layout() {
    return isset($this->db[self::hide_char . 'layout']) ?
      strtolower($this->db[self::hide_char . 'layout']) : 'table';
  }

  # TODO: support array of arguments
  public function template($paths) {
    if (!is_array($paths)) {
      $paths = array($paths);
    }
    $html = '';
    foreach ($paths as $path) {
      $template = self::$template;
      foreach (explode('/', $path) as $key) {
        if ($key == self::hide_char . 'layout') {
          $key = $this->get_layout();
        }
        if (isset($template[$key])) {
          $template = $template[$key];
        } else {
          # not found, do nothing
          break;
        }
      }
      if (func_num_args() > 1) {
        $args = func_get_args();
        unset($args[0]);
        $html .= vsprintf($template, $args);
      } else {
        # optimization
        $html .= $template;
      }
    }
    return $html;
  }  

  public function start_form() {
    echo $this->template(array('form/start', 'form/.layout/start'));
  }

  public function end_form() {
    echo $this->template(array('form/.layout/end', 'form/end'));
  }

  public static function attr_quote($text) {
    /*
      Better to avoid line breaks within attribute values.
      See http://www.w3.org/TR/xhtml1/guidelines.html#C_5
     */
    return htmlspecialchars(str_replace(
            array("\n", "\r"), array(' ', ' '), $text)
    );
  }

  protected static function add_attr($allowed_attrs, $def) {
    $attrs = array();
    if (!is_array($allowed_attrs)) {
      $allowed_attrs = array($allowed_attrs);
    }
    foreach ($allowed_attrs as $fw_attr => $html_attr) {
      if (gettype($fw_attr) == 'integer') {
        $fw_attr = $html_attr;
      }
      if (isset($def[$fw_attr])) {
        /*
          Quote value. Better to avoid line breaks within attribute values.
          See http://www.w3.org/TR/xhtml1/guidelines.html#C_5
         */
        $attrs[] = sprintf('%s="%s"', $html_attr,
          self::attr_quote($def[$fw_attr]));
      }
    }
    return implode(' ', $attrs);
  }

  protected function star_expansion($tbl_name, &$col_names) {
    $tbl = $this->db[$tbl_name];
    # TODO: * selects all col_names, *-field all but a field
    if ($col_names == '*') {
      $col_names = array();
      foreach ($tbl as $col_name => $col_def) {
        if ($col_name[0] != self::hide_char) {
          $col_names[] = $col_name;
        }
      }
    } else {
      self::array_conv(',', $col_names);
    }
  }

  public function add_ctrl($controls) {
    $layout = $this->get_layout();
    $def_label = $this->def('label', TRUE);
    foreach ($controls as $tbl_name => $col_names) {
      if ($tbl_name == self::hide_char . 'push') {
        echo $this->template('form/.layout/push_start');
        self::array_conv(',', $col_names);
        foreach ($col_names as $type => $push_button) {
          if (!is_array($push_button)) {
            if (gettype($type) == 'integer') {
              $push_button = array('type' => $push_button);
            } else {  # gettype($type) == 'string'
              $push_button = array('type' => $type, 'value' => $push_button);
            }
          }
          printf('<input type="%s" %s/>',
            isset($push_button['type']) ? $push_button['type'] : 'submit',
            self::add_attr(array('value', 'name'), $push_button));
        }
        echo $this->template('form/.layout/push_end');
      } else {
        $this->star_expansion($tbl_name, $col_names);
        foreach ($col_names as $col_name) {
          /*
            Note that PHP notation af[tbl][col] (square brackets in form names) 
            violates HTML specs :-P
           
            http://www.w3.org/TR/html4/types.html#type-name
            See also http://www.php.net/manual/en/faq.html.php#faq.html.arrays
           */
          $id = sprintf('fw_%s.%s', self::attr_quote($tbl_name),
            self::attr_quote($col_name));
          # TODO: should brackets be converted to entities?
          $name = sprintf('af[%s][%s]', self::attr_quote($tbl_name),
            self::attr_quote($col_name));
          $col_def = $this->db[$tbl_name][$col_name];
          if (!isset($col_def['null']) || !$col_def['null']) {
            $required = $this->template('form/.layout/required');
          } else {
            $required = '';
          }
          /*
            Required marker can be put inside a label or
            at the beginning or the end of a control
           */
          echo $this->template('form/.layout/grp_start', $required);
          $label = isset($col_def['label']) ? $col_def['label']
            : ($def_label ? $col_name : '');
          if ($label != '') {
            echo $this->template('form/.layout/label', $id, $label, $required);
          } else {
            echo $this->template('form/.layout/nolabel', $required);
          }

          echo $this->template('form/.layout/ctrl_start', $required);
          switch (strtoupper($col_def['type'])) {
            case 'STR':
              # TODO: validate
              # TODO: set value from SQL DEFAULT?
              # TODO: add other attributes
              # TODO: make a template
              printf('<input type="text" id="%s" name="%s" %s/>', $id, $name,
                self::add_attr(array('maxlen' => 'maxlength', 'size'),
                $col_def));
            break;

            case 'INT':
              # TODO: validate
              echo '<input type="text" />';
            break;

            case 'DEC':
              # TODO:
            break;

            case 'REAL':
              # TODO:
            break;

            case 'DATE':
              # TODO:
            break;

            case 'YEAR':
              # TODO:
            break;

            case 'BOOL':
              # TODO: In MySQL BOOL data type a value of zero is considered 
              # false. Non-zero values are considered true.
              # TODO:
            break;

            case 'PWD':
              printf('<input type="password" id="%s" name="%s" %s/>', $id, $name,
                self::add_attr(array('maxlen' => 'maxlength', 'size'),
                $col_def));
            break;

            case 'URL':
              # TODO:
            break;

            case 'EMAIL':
              # TODO:
            break;

            case 'ENUM':
              # TODO:
            break;

            case 'BIN':
              # TODO: use a file control? See what phpmyadmin uses.
            break;

            case 'HASH':
              # TODO:
            break;
          }
          echo $this->template(array('form/.layout/ctrl_end', 'form/.layout/grp_end'), $required);
        }
      }
    }
  }

  /*
    Returns the definition of a filter suitable
    for validation with filter_input_array.
   */
  public function get_filter($controls) {
    $filter = array();
    foreach ($controls as $tbl_name => $col_names) {
      if ($tbl_name == self::hide_char . 'push') {
        # TODO
      } else {
        $this->star_expansion($tbl_name, $col_names);
        foreach ($col_names as $col_name) {
          $col_def = $this->db[$tbl_name][$col_name];
          switch (strtoupper($col_def['type'])) {
            case 'STR':
              # TODO: consider sanitizing (make an options to select behaviour 
              # between validation and sanitizing)
              # TODO: length check
              $filter[$col_name] = FILTER_DEFAULT;
            break;

            case 'INT':
            case 'YEAR':
              if (isset($col_def['minval'])) {
                $options['min_range'] = $col_def['minval'];
              }
              if (isset($col_def['maxval'])) {
                $options['max_range'] = $col_def['maxval'];
              }
              $filter[$col_name] = array('filter'  => FILTER_VALIDATE_INT,
                'options' => $options);
            break;

            case 'DEC':
              # TODO:
            break;

            case 'REAL':
              # TODO:
            break;

            case 'DATE':
              # TODO:
            break;

            case 'BOOL':
              # TODO: In MySQL BOOL data type a value of zero is considered 
              # false. Non-zero values are considered true.
              $filter[$col_name] = array('filter'  => FILTER_VALIDATE_BOOLEAN);
            break;

            case 'PWD':
              # TODO:
            break;

            case 'URL':
              # TODO: validates file:// also?
              $filter[$col_name] = FILTER_VALIDATE_URL;
            break;

            case 'EMAIL':
              $filter[$col_name] = FILTER_VALIDATE_EMAIL;
            break;

            case 'ENUM':
              # TODO:
            break;

            case 'BIN':
              # TODO:
            break;

            case 'HASH':
              # TODO:
            break;
          }
        }
      }
    }
    return $filter;
  }

  public static function include_files($path='', $files = 'css,js') {
    self::array_conv(',', $files);
    foreach ($files as $file) {
      switch ($file) {
        case 'css':
          echo "<link rel=\"stylesheet\" href=\"${path}fw.css\" type=\"text/css\" />\n";
        break;

        case 'js':
          /*
            We use compressed version of MooTools to save time and space.
            Note we are using the version that comes with MochaUI
            to avoid having duplicates.
           */
          echo <<< EOH
<script type="text/javascript" src="${path}mocha_v.0.9.5/scripts/mootools-1.2-core.js"></script>
<script type="text/javascript" src="${path}mocha_v.0.9.5/scripts/mootools-1.2-more.js"></script>
<script type="text/javascript" src="${path}fw.js"></script>

EOH;
        break;
      }
    }
  }

  /*
     TODO: support for unbuffered result sets
    (beware that num_rows's return value changes in this case)

	 	TODO:
		Tables can be used as multiple selection lists ala phpMyAdmin.
		See function PMA_markRowsInit in js/functions.js.
		For this to work your result must include all the fields needed to uniquely 
		identify a row. You must tell formwriter what fields make up the unique key 
		for your result with the uniq array option. You may disable displaying for 
		all or any of these key fields if you want. Note when a table is paginated 
		(see print_page method), you can choose if selection can cross multiple 
		pages. Default is no: selection is reset when you switch to another page. 
		For user convenience your application can provide a link to reset the 
		selection to none.
   */
  public function print_table($result, $option = array()) {
    # Merge options with defaults.
    $option = array_merge(self::$table_def_opts, $option);
 
    if ($result->num_rows || is_null($option['empty'])) {
      echo $this->template('table/start', self::add_attr('summary', $option));

      if ($option['caption'] != '') {
        echo $this->template('table/caption', $option['caption']);
      }

      $fields = $result->fetch_fields();
      # We are only interested in field names.
      $i = 0;
      foreach ($fields as $field) {
        $fields[$i++] = $field->name;
      }

      $custom = $option['custom'];
			$uniq = $option['uniq'];
			self::array_conv(',', $uniq);
			if ($uniq) {
				echo '<colgroup></colgroup>';
			}
      if (is_null($option['group'])) {
        foreach ($fields as $field) {
          if (! array_key_exists($field, $custom) || is_string($custom[$field])) {
            echo '<colgroup></colgroup>';
          }
        }
      } elseif (is_array($option['group'])) {
        foreach ($option['group'] as $span) {
          printf('<colgroup %s></colgroup>', $span != 1 ? "span=\"$span\"" : '');
        }
      } else {
        echo $option['group'];
      }
      echo "\n";

      # Cycle through results.
      $result->data_seek(0);
      $repeat = $option['repeat'];
      $header = $option['header'];
      $body = TRUE;
      $i = 0;
      do {
        $row = $result->fetch_row();

        if ($header) {
          $header = $repeat > 0;

          if (($i == 0 || $i % $repeat == 0) && ($body || $row)) {
            echo $this->template('table/head_start');
						if ($uniq) {
							echo $this->template('table/head', '');
						}
            foreach ($fields as $field) {
              if (! array_key_exists($field, $custom) || is_string($custom[$field])) {
                echo $this->template('table/head', $field);
              }
            }
            echo $this->template('table/head_end');
          }
        }
        if ($row) {
          if ($body) {
            $body = FALSE;

            echo $this->template('table/body_start');
          }
          echo $this->template('table/row_start', $i % 2 ? 'fw_even' : 'fw_odd');
					if ($uniq) {
          	echo $this->template('table/checkbox');
					}
          reset($fields);
          foreach ($row as $col => $value) {
            $field = current($fields);
            if (array_key_exists($field, $custom)) {
              eval($custom[$field]);
            } elseif (is_null($value)) {
              echo $this->template('table/null');
            } else {
              echo $this->template('table/data', self::attr_quote($field), $value);
            }
            next($fields);
          }
          echo $this->template('table/row_end');

          $i++;
          if ($repeat) {
            $i = $i % $repeat;
          }
        }
      } while ($row);

      if (! $body) {
        echo $this->template('table/body_end');
      }
      echo $this->template('table/end');
    } else {
      echo $option['empty'];
    }

    if ($option['free']) {
      # Free result set.
      $result->close();
    }
  }

  protected function print_pager($id, $display, $row_total, $offset, $row_cnt, $delta, $url) {
    # TODO: align pager at the center of the table.

    # If there is only one page, nothing to do :-)
    if ($row_total <= $row_cnt) {
      return;
    }

    # Emit HTML code.

    $current = $offset / $row_cnt + 1;
    $fullid = $id . '_' . $display;
    $first = max($current + 1 - $delta, 1);
    $last = min($current + $delta - 1, ceil($row_total / $row_cnt));
    if ($offset) {
      echo $this->template('pager/first', $fullid, $last);
      echo $this->template('pager/back', $fullid, $current - 1, $last);
    }

    for ($page = $first; $page <= $last; $page++) {
      if ($page == $current) {
        echo $this->template('pager/current', $fullid, $page);
      } else {
        echo $this->template('pager/number', $fullid, $page, $last);
      }
    }

    if ($current < $last) {
      echo $this->template('pager/forward', $fullid, $current + 1, $last);
      echo $this->template('pager/last', $fullid, $last);
    }

    # Emit JavaScript code.
    $last_offset = (int)($row_total / $row_cnt) * $row_cnt;
    $url = addcslashes($url, "'");
    echo "<script type=\"text/javascript\">fw_substs('$id', '$display', '$url', $first, $current, $last, $offset, $row_cnt, $last_offset)</script>\n";
  }

  # An AJAX paginator, with page numbers' list (akin to Google search result).
  public function print_page($query, $option) {
    # TODO: row_count may also be a parameter

    /*
      offset and row_count must be non-negative integers;
      the offset of the initial row is 0 not 1.
     */
    $filters = array(
      'fw_o' => array(
        'filter'  => FILTER_VALIDATE_INT,
        'options' => array(
          'min_range' => 0
        )
      )
    );

    $input = filter_input_array(INPUT_GET, $filters);
    $row_cnt = $option['row_cnt'];

    if (is_null($offset = $input['fw_o'])) {
      $offset = 0;
    } elseif ($offset === FALSE || $offset % $row_cnt) {
      trigger_error('Invalid paginator offset');
      return;
    }

    # Merge options with defaults.
    $option = array_merge(self::$table_def_opts, $option);

    /*
      MySQL extension: obtain the total number of rows without running the 
      query again (without LIMIT), which would be slower. See help about 
      FOUND_ROWS() function in MySQL manual for details. Note the LIMIT 
      statement is applied to the global query as a whole and we use the 
      SQL_CALC_FOUND_ROWS on the first (outer) SELECT only.
     */
    $query = sprintf('(%s) LIMIT %u, %u', $query, $offset, $row_cnt);
    $query = preg_replace('/SELECT/', 'SELECT SQL_CALC_FOUND_ROWS', $query, 1);

    $result = $this->mysqli->query($query);

    # TODO: is there a simpler way to get a one-field result?
    $row_total = $this->mysqli->query('SELECT FOUND_ROWS()')->fetch_row();
    $row_total = $row_total[0];

    /*
       TODO: check id token syntax: must begin with a letter ([A-Za-z]) and may 
       be followed by any number of letters, digits ([0-9]), hyphens ("-"), 
       underscores ("_"), colons (":"), and periods ("."). Warn otherwise.
     */
    $id = $option['id'];
    $delta = $option['delta'];
     echo $this->template('pager/start', $id);

    $pager = $option['pager'];
    $url = $option['url'];
    if ($url == '') {
      $url = basename($_SERVER['PHP_SELF']);
    }
    if ($pager == 'top' || $pager == 'both') {
      $this->print_pager($id, 'top', $row_total,
        $offset, $row_cnt, $delta, $url);
    }

    $this->print_table($result, $option);

    if ($pager == 'bottom' || $pager == 'both') {
      $this->print_pager($id, 'bot', $row_total,
        $offset, $row_cnt, $delta, $url);
    }

     echo $this->template('pager/end');
  }

  public static function is_numeric($type) {
    # TODO: update when new types will be added
    return in_array(strtoupper($type),
      array('INT', 'DEC', 'REAL', 'YEAR', 'BOOL'));
  }

  # Automatic quoting (if required by type) and escaping.
  public function quote_escape_qbe($value, $type = 'STR') {
    /* To optmize we search for types that
      do not require quoting, which are fewer.
      TODO: check for this to be true in the future,
      when new types will be added
      TODO: make sure all supported types that do not need quote
      around values are listed in function self::is_numeric
      TODO: problems when called statically
     */
    $value = $this->mysqli->real_escape_string($value);
    $value = self::is_numeric($type) ? $value : "'$value'";

    return $value;
  }

  protected static function select_qbe($distinct, $select,
      $table_ref, $where, $group, $having, $order) {
    ksort($order);
    return sprintf('SELECT%s %s FROM %s%s%s%s%s', $distinct,
      implode(',', $select), $table_ref,
      count($where) ? ' WHERE ' . implode(' AND ', $where) : '',
      count($group) ? ' GROUP BY ' . implode(',', $group) : '',
      count($having) ? ' HAVING ' . implode(',', $having) : '',
      count($order) ? ' ORDER BY ' . implode(',', $order) : '');
  }

  public function is_part_of_unique_key($tbl_name, $col_name) {
    # TODO: if .key is an array, scan for a primary or unique key including col_name
    $tbl_def = $this->db[$tbl_name];
    $key = self::hide_char . 'key' ;
    return isset($tbl_def[$key]) ? $tbl_def[$key] == $col_name : FALSE;
  }

  /*
    Converts a query-by-example to SQL. For theory behind and syntax see:
    http://www.research.ibm.com/journal/sj/164/ibmsj1604C.pdf
    http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/supporting_material.htm
    We support syntaxes and features described in both these papers.
    This is a full version 2 QBE implementation, not a partial support for 
    form-based queries as that offered by Microsoft Access or phpMyAdmin.
   
    Note that this method cannot be called statically. A FormWriter object is 
    required because some QBE features (as auto-quoting) require knowledge of 
    database schema and and active connection to the database.
   
    TODO: implementation is not complete yet.
   */
  public function convert_qbe($qbe) {
    # Support for two simple queries on schema. Note P. P. is not supported.
    if (! is_array($qbe)) {
      if (strrchr($qbe, ' ') == ' P.') {
        return sprintf('SHOW COLUMNS FROM `%s`',
          substr($qbe, 0, strrpos($qbe, ' ')));
      }
      if (strncmp($qbe, 'P.', 2)) {
        # TODO: error handling
        return;
      }
      return 'SHOW TABLES';
    }

    # Queries on data.

    /*
      Implementation of implicit AND and/or OR operations.
      That can probably be done with a phase of optimization
      and shrinking before parsing the query.

      Support for OR-queries without condition-box: two or more example 
      rows of the same table contain a P. and none or different 
      variables: reduce them to a single row with a P. in the same 
      column. For all other columns with a condition, build a condition 
      box containing an OR of all these conditions and replace the 
      conditions with a variable.

      Support for AND-queries without condition-box: two or more 
      example rows of the same table contain the same variable: reduce 
      them to a single row with the variable in the same column. For 
      all other columns with a condition, build a condition box 
      containing an AND of all these conditions and replace the 
      conditions with a variable.

      However AND-OR ops are NOT implemented for now for the following reasons:

      - it is no clear to me when to translate to a self-join or an AND 
        operation. Rules stated before are a little ambiguos. AND can be 
        implemented as self-joins of course, but this could be inefficient 
        unless an optimization is applied by MySQL. For example test case 
        convert_qbe-Sea.9 should be regarded as a self-join and NOT as an 
        AND-op. How to tell?
      - they are not simple to implement and provide no additional query power. 
        They only are an alternative (more verbose) syntax; one can use 
        condition boxes instead

      In particular please note that AND ops are NOT implemented. If you use 
      them, they will seem to work, but this happens because they will be 
      translated to equivalent self-joins which is not efficient. So do not use 
      them. Specify AND ops explicity through condition boxes.
     */

    # Phase 1: parse QBE query.

    $select = array();
    $output = array(); # Select list from an output table.
    # As per Zloof specification, by default do not repeat duplicates
    $distinct = ' DISTINCT';
    $table_ref = '';
    $join_cond = array();
    $from_delete = array();
    $to_update = array();
    $set = array(); # For UPDATE
    $val = array(); # Values to INSERT.
    settype($qbe['.cond'], 'array');
    $where = array();
    $group = array();
    $having = array();
    $var = array();  # Variable lookup table.
    $order = array();
    $alias_num = 1;
    $current_query_type = '';
    $query_type = '';
    $sub_query_type = '';
    $col = array(); # Columns to INSERT.

    reset($qbe);
    while (list($tbl_name, $col_names) = each($qbe)) {
      if ($tbl_name == '.cond') {
        continue;
      }

      if (preg_match(sprintf('/^\%s(.*)_\d+$/', self::hide_char), $tbl_name, $matches)) {
        $tbl_name = $matches[1];
        $first_row = $query_type != 'I';
      } else {
        $first_row = TRUE;
      }

      $tbl_exists = isset($this->db[$tbl_name]);  # if FALSE it is an output table

      $print_all = FALSE;
      if ($tbl_exists) {
        # TODO: what if table does not exists?
        # Default query type is SELECT
        $current_query_type = 'S';

        # Default join type is INNER | CROSS
        $join_type = 'I';
        $joined_join_type = 'I';

        $alias = "T$alias_num";
      }

      /*
        reset($col_names);
        while (list($col_name, $col_def) = each($col_names)) {

        is not useful because each() does NOT return a reference to the array 
        contents, but a copy of the item and there is no way to do reference 
        assignment using the list function
       */
      foreach ($col_names as $col_name => $col_def) {
        # TODO: output tables with arrays make sense?
        if (is_array($col_def)) {
          # Expand multiple tables with the same name
          foreach ($col_def as $i => $def) {
            if ($i != 0) {
              $tbl_alias = sprintf('%s%s_%u', self::hide_char, $tbl_name, $i);
              $qbe[$tbl_alias][$col_name] = $def;
            }
          }

          /*
            Note we use array_key_exists instead of isset because with
            the latter if the key equals NULL is considered unset.
           */
          if (array_key_exists(0, $col_def)) {
            # TODO: really needed?
            $qbe[$tbl_name][$col_name] = $col_def = $col_def[0];
          } else {
            continue;
          }
        }

        if ($col_name == self::hide_char . 'rel') {
          foreach (explode('.', $col_def) as $cmd) {
            switch ($cmd) {
              case 'P':
                if ($tbl_exists) {
                  $select[] = "$alias.*";
                } else {
                  $print_all = TRUE;
                }
              break;

              case 'UN':
              case 'UNQ':
                $distinct = ' DISTINCT';
              break;

              case 'I':
                if (! $tbl_exists) {
                  trigger_error("Cannot insert in the output table `$tbl_name'");
                }
                $current_query_type = 'I';
              break;

              case 'D':
                if (! $tbl_exists) {
                  trigger_error("Cannot delete in the output table `$tbl_name'");
                }
                $current_query_type = 'D';
                $from_delete[] = $alias;
              break;

              /*
                Please note the command U is not supported at the field level,
                only at the relation level because the latter is easier to
                implement. This should not make any difference, because QBE
                does not allow key fields updating anyway.

                Also the field-level U command poses an ambiguity problem:
                if U.NULL means update the field to NULL, how can we
                update a field to the string NULL (not a NULL value)?
                A simple solution would be to just add a trailing space, e.g. 
                'U.NULL ' but this poses another escape requirement to the 
                user. That's why I think the relation-level U command is 
                preferable (avoids ambiguity with NULL values and you
                have to type only one 'U.' command when updating more than
                one field of the same table).
               */
              case 'U':
                if (! $tbl_exists) {
                  trigger_error("Cannot update in the output table `$tbl_name'");
                }
                $current_query_type = 'U';
              break;

              case 'ALL':
                $distinct = '';
              break;

              case '!':
              case '!=':
                # TODO: makes sense for an output table?
                /*
                  The not operator is implemented using outer joins. They are
                  preferable over using subqueries with the NOT EXISTS
                  operator, because they simplify the translation to SQL.

                  This is no surprise if you consider the following argument: 
                  joins are declarative, while subqueries have a procedural 
                  flavor; QBE is purely declarative; the translation from a 
                  declarative to a mostly declarative language (like SQL as 
                  generated by this method) it is simpler than translating
                  to a hybrid or mostly procedural language.
                  
                  For the same reason we avoided using subqueries and
                  the IN operator when a table has to be joined with
                  itself (auto-join): we use table alias and JOINs
                  instead to achieve the same result.
                 */
                $join_type = 'O';  # OUTER
              break;

              case '':
              break;

              default:
                trigger_error(sprintf("Unknown QBE command `%s' for relation `%s'",
                  $cmd, $tbl_name));
              break;
            }
          }

          continue;
        }

        # We support unnamed columns for display by testing
        # for col_name to be an empty string or to exist
        # (this is an extension to allow column renaming).
        $col_exists = isset($this->db[$tbl_name][$col_name]);
        if ($tbl_exists && $col_exists) {
          $col_type = $this->db[$tbl_name][$col_name]['type'];

          if ($current_query_type == 'I') {
            # Treat it like a constant to insert.
            # TODO: checking field length on insertion
            # TODO: first_row variable needed with multiple insert
            if ($first_row) {
              $col[] = "`$col_name`";
            }
            # Support for inserting NULL values.
            # TODO: how to escape a variable name to consider as a literal string?
            $val[] = is_null($col_def) ? 'NULL' : (preg_match(self::var_re, 
              $col_def) ? $col_def : $this->quote_escape_qbe($col_def, 
              $col_type));

            continue;
          }

          # Most of the times tbl_name is redundant.
          $col_spec = "$alias.`$col_name`";

          if ($current_query_type == 'U' &&
            ! $this->is_part_of_unique_key($tbl_name, $col_name)) {
            # Treat it like a constant to update.

              /*
               * TODO
            if (preg_match(self::expr_re, $col_spec)) {
              # Arithmetic expression.
              if ($tbl_exists) {  # TODO: is this test needed?
                $qbe['.cond'][] = "$col_spec$op$cmd";
              }

              continue;
            }
               */

            # Support for updating to NULL values.
            $set[] = is_null($col_def) ? "$col_spec=NULL" : sprintf('%s=%s', 
              $col_spec, $this->quote_escape_qbe($col_def, $col_type));

            continue;
          }
        }

        /*
          Implicitely print the fields of a selection
          in a INSERT... SELECT... query.

          TODO: if a SELECT comes before an INSERT this does not work and any 
          field to insert should be explicity prefixed by a P. Can we easily 
          implement implicitely printing in this case also?
         */
        $print = $query_type == 'I' ? TRUE : $print_all;
        $funct = ''; $funct_distinct = '';
        if (is_null($col_def)) {
          $col_def = '=NULL';
        }
        foreach (explode('.', $col_def) as $cmd) {
          switch ($cmd) {
            case 'P':
               $print = TRUE;
            continue 2;

            case 'UN':
            case 'UNQ':
              $funct_distinct = 'DISTINCT ';
            continue 2;

            case 'ALL':
            continue 2;

            case 'CNT':
            case 'COUNT':
              $funct = 'COUNT';
            continue 2;

            case 'G':
              # TODO: is grouping allowed in an output table?
              $group[] = $col_spec;
            continue 2;

            case 'SUM':
              $funct = 'SUM';
            continue 2;

            case 'MAX':
              $funct = 'MAX';
            continue 2;

            case 'MIN':
              $funct = 'MIN';
            continue 2;

            case 'AVG':
              $funct = 'AVG';
            continue 2;
          }

          if (preg_match('/^([AD]O)(?:\((\d+)\))?$/', $cmd, $matches)) {
            $cmd = $matches[1];
            $number = isset($matches[2]) ? $matches[2] : 0;
            $direction = $cmd == 'DO' ? ' DESC' : '';
            $orderby = "$col_spec$direction";
            if ($number) {
              $order[$number] = $orderby;
            } else {
              $order[] = $orderby;
            }

            continue;
          }

          if ($tbl_exists && $col_exists) {
            if ($print) {
              $select[] = $funct ? "$funct($funct_distinct$col_spec)" : $col_spec;
              $print = FALSE; # To avoid printing a field more than once.
            }
          } else {
            if ($print) {
              # Arithmetic expression or variable to link and print.
              $output[$col_name] = $funct ? "$funct($funct_distinct$cmd)" : $cmd;
              $print = FALSE; # To avoid printing a field more than once.
            } else {
              $var_name = $cmd; # Name of the last seen variable.
            }

            continue;
          }

          /*
            Other useful operators as "starts with" and "contains" are
            implemented below with partially underlined qualified retrieval.
           */
          if ($op_len = strspn($cmd, '<>=!', 0, 2)) {
            # Extract operator.
            if (($op = substr($cmd, 0, $op_len)) == '!') {
              $op = '!=';
            }
            $cmd = substr($cmd, $op_len);
          } else {
            $op = '=';  # Default operator.
          }

          if (preg_match(self::expr_re, $cmd)) {
            # Arithmetic expression.
            if ($tbl_exists) {  # TODO: is this test needed?
              $qbe['.cond'][] = "$col_spec$op$cmd";
            }

            continue;
          }

          # Variable double underlining for explicit `group-by' operator.
          $like = preg_replace(self::group_var_re, '%', $cmd, -1, $count);
          if ($count) {
            # TODO: is grouping allowed in an output table?
            $group[] = $col_spec;
          } else {
            $like = preg_replace(self::var_re, '%', $cmd, -1, $count);
          }
          if ($count) {
            # Variable.

            # Add join condition.
            if (isset($var[$cmd])) {
              list($joined_col_spec, $joined_op, $joined_join_type) = $var[$cmd];
              $join_cond[] = $op != '='
                ? sprintf('%s%s%s', $col_spec, $op, $joined_col_spec)
                : sprintf('%s%s%s', $joined_col_spec, $joined_op, $col_spec);
              if ($join_type == 'O') {
                if ($joined_join_type == 'I') {
                  $where[] = "$col_spec IS NULL";
                }
              } elseif ($joined_join_type == 'O') {
                # $join_type == 'I'
                $where[] = "$joined_col_spec IS NULL";
              }
            } else {
              # Compact multiple SQL wildcard % into one
              $like = preg_replace('/%+/', '%', $like);
              if ($like != '%') {
                $join_cond[] = sprintf('%s LIKE %s', $col_spec,
                  $this->quote_escape_qbe($like));
              }
            }
            $var[$cmd] = array($col_spec, $op, $join_type);
          } elseif ($cmd != '') {
            # Constant (including NULL).

            $join_cond[] = $cmd != 'NULL' ? sprintf('%s%s%s', $col_spec, $op,
              $this->quote_escape_qbe($cmd, $col_type)) : $col_spec .
              ($op == '=' ? ' IS NULL' : ' IS NOT NULL');

            # No further command parsing should be done.
            break;
          }
        }
        if ($print) {
          # Print was the last command for this column.
          if ($tbl_exists && $col_exists) {
            $select[] = $funct ? "$funct($funct_distinct$col_spec)" : $col_spec;
          } else {
            # Arithmetic expression or variable to link and print.
            $output[$col_name] = $funct ? "$funct($funct_distinct$var_name)" : $var_name;
          }
        }
      }

      if ($tbl_exists) {
        if ($query_type != 'S' && $query_type != '' && $current_query_type == 'S') {
          $sub_query_type = $current_query_type;
        } else {
          if ($query_type == 'S' && $current_query_type != 'S') {
            $sub_query_type = 'S';
          }
          $query_type = $current_query_type;
        }

        if ($current_query_type != 'S') {
          $tbl_to_mod = "`$tbl_name`";
        }

        if ($current_query_type != 'I') {
          $alias_num++;
          if ($table_ref != '') {
            # TODO: test
            $table_ref .= $join_type == 'I' ? ($joined_join_type == 'O' ?
              ' RIGHT JOIN ' : ' JOIN ') : ' LEFT JOIN ';
            $table_ref .= "`$tbl_name` $alias";
            if (count($join_cond)) {
              $table_ref .= ' ON ' . implode(' AND ', $join_cond);
            }
            $join_cond = array();
          } else {
            $table_ref .= "`$tbl_name` $alias";
          }
        }
      }
    }

    # Phase 2: resolve all pending references.

    # Resolve all variables references in output table.
    foreach ($output as $alias => $expr) {
      foreach ($var as $var_name => $var_info) {
        $expr = str_replace($var_name, $var_info[0], $expr);
      }
      # Note how we avoid substitution to occur on the column alias.
      $select[] = $expr . ($alias != '' ? " AS `$alias`" : '');
    }

    # Resolve all variables references in condition box.
    foreach ($qbe['.cond'] as $cond) {
      /*
        Detect conditions with aggregate function for use in HAVING construct.
        For faster execution of SQL commands we always use more conditions in 
        WHERE clause than in HAVING clause.
       */
      if (preg_match(self::funct_re, $cond, $matches)) {
        # Change syntax for aggregate function calls in condition box.
        foreach (array(1, 3, 5) as $i) {
          if (isset($matches[$i]) && $funct_distinct = $matches[$i] == 'UN'
              || $matches[$i] == 'UNQ' ? 'DISTINCT ' : '') {
            break;
          }
        }
        $cond = preg_replace(self::funct_re,
          "\$2($funct_distinct\$4)", $cond);
        $having[] = preg_replace('/CNT\(/', 'COUNT(', $cond);
      } else {
        $where[] = $cond;
      }
    }
    foreach ($set as &$cond) {
      foreach ($var as $var_name => $var_info) {
        $cond = str_replace($var_name, $var_info[0], $cond);
      }
    }
    # TODO: how to escape a variable name to consider as a literal string?
    foreach ($val as &$cond) {
      foreach ($var as $var_name => $var_info) {
        $cond = str_replace($var_name, $var_info[0], $cond);
      }
    }
    foreach ($where as &$cond) {
      foreach ($var as $var_name => $var_info) {
        $cond = str_replace($var_name, $var_info[0], $cond);
      }
    }
    foreach ($having as &$cond) {
      foreach ($var as $var_name => $var_info) {
        $cond = str_replace($var_name, $var_info[0], $cond);
      }
    }

    # Augment where conditions with the rest of the join conditions.
    $where = array_merge($where, $join_cond);

    # Phase 3: assemble SQL query

    switch ($query_type) {
      case 'S':
      return self::select_qbe($distinct, $select, $table_ref,
        $where, $group, $having, $order);

      case 'I':
        # TODO: support for inserting DEFAULT values
        if ($sub_query_type == 'S') {
          $insert_query = sprintf('INSERT INTO %s (%s) %s', $tbl_to_mod,
            implode(',', $col), self::select_qbe($distinct, $val,
            $table_ref, $where, $group, $having, $order));
        } else {
          $insert_query = sprintf('INSERT INTO %s (%s) VALUES', $tbl_to_mod,
            implode(',', $col));
          # Support for inserting multiple rows.
          for ($i = 0; $i < count($val); $i++) {
            $insert_query .= ($i % count($col) ?
              ($i ? ',' : '')
            :
              ($i ? '),(' : '(')
            ) . $val[$i];
          }
          $insert_query .= ')';
        }
      return $insert_query;

      case 'D':
        # TODO: support deleting from multiple tables?
        if ($sub_query_type == 'S') {
          return sprintf('DELETE FROM %s USING %s%s',
            implode(',', $from_delete), $table_ref,
            count($where) ? ' WHERE ' . implode(' AND ', $where) : '');
        }
        if (count($where)) {
          return sprintf('DELETE FROM %s USING %s WHERE %s', implode(',', $from_delete), $table_ref, implode(' AND ', $where));
        }
        /*
          Note we use TRUNCATE TABLE when deleting all rows from a single table because it
          is faster for an InnoDB table with no FOREIGN KEY constraints.
         */
      return 'TRUNCATE TABLE ' . $tbl_to_mod;

      case 'U':
        # TODO: support for update a column to a DEFAULT value
        # TODO: support for (MySQL extensions?) ORDER BY, LIMIT and multiple tables
      return sprintf('UPDATE %s SET %s%s', $table_ref, implode(',', $set),
        count($where) ? ' WHERE ' . implode(' AND ', $where) : '');
    }
  }

  # Executes a query-by-example.
  public function exec_qbe($qbe) {
    # TODO: DEBUG:
    $query=$this->convert_qbe($qbe);
    echo $query, '<br />';
    return $this->mysqli->query($this->convert_qbe($qbe));
  }
}

# End of file
# vim: set ts=2 sw=2 et:
?>
